| Mybatis批量插入或更新数据 | 您所在的位置：网站首页 › mybatis insert 批量 foreach › Mybatis批量插入或更新数据 |
Mybatis批量插入或更新数据
|
对于大量的数据，使用批量插入或修改可以提高效率。原因是批量添加或修改是执行一条sql语句，传入多个值，可以减少与数据库的访问次数，从而会提高效率。下面分别介绍Oracle和MySQL的用法： 1.Oracle批量插入数据对于集合类型的数据，在插入时会使用mybatis的标签，那么正确的用法如下： insert into user(id,name,password,addr) select user_seq.nextval,a.* from( select #{item.name}, #{item.password}, #{item.addr} from dual ) a以上语句是向user表循环查询数据，传递的参数是List类型的集合。 需要注意是的，在使用时，分隔符separator必须是union all。 2.MySQL批量插入数据 2.1 使用foreach对于集合类型的数据，在插入时会使用mybatis的标签，那么mysql的批量插入数据sql如下： insert into user(name,password,addr) values ( #{item.name}, #{item.password}, #{item.addr} )和oracle的语句相比，没有指定主键，原因是主键id使用了自增。在foreach中里面的两个括号不能省略，要使用逗号作为分隔符。 批量处理的java代码如下： public void test(List list) { Integer maxSize = 2000;//每次处理的数据条数，也是分批处理 mysql支持单个sql插入中最大values是2000 Integer queryCnt = list.size() % maxSize == 0 ? list.size() / maxSize : (list.size() / maxSize) + 1; for (int i = 0; i < queryCnt; i++) { Integer endIndex = (i + 1) * maxSize; if (endIndex > list.size()) { endIndex = list.size(); } //集合截取 List tempList = new ArrayList(list.subList(i * maxSize, endIndex)); userDao.insertBatch(tempList); } }对于foreach而言，当表的列数在20列以上，一次性插入的行数在5000条以上时，整个插入的过程比较耗时，预计在10分钟以上。因此，foreach可使用在一次插入20~50条数据的场景中。 2.2使用批处理（推荐）foreach对大批量多列的数据插入效率不高，这是可采用mybatis自带的批量插入方式。 1）在配置文件中开始批量插入 将rewriteBatchedStatements设置为true，这步必不可少，否则批量插入不生效 spring.datasource.url=jdbc:mysql://localhost:3306/db2020?useUnicode=true&rewriteBatchedStatements=true2）生成数据，进行批量插入 @Test public void test3() { //数据生成 List list = createData(30000); StopWatch stopWatch = new StopWatch(); //使用批处理 stopWatch.start(); SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); try { UserDao userDao = sqlSession.getMapper(UserDao.class); list.stream().forEach(user-> userDao.insert(user)); sqlSession.commit();//批量提交 } catch (Exception e) { log.error("发生异常:{}", e); } finally { sqlSession.clearCache();//清除缓存 sqlSession.close();//关闭连接 } stopWatch.stop(); log.info("批处理耗时：{} ms", stopWatch.getTotalTimeMillis()); } private List createData(int size) { List list = new ArrayList(); User user; for (int i = 0; i < size; i++) { user = new User(); user.setName("小王" + i); user.setPassword("983" + i); user.setUsername("zhangs" + i); user.setAge(20 + i % 5); user.setImgurl("www.baidu.com"); list.add(user); } return list; }本人亲测，3000条数据使用批量插入不到1秒即可完成。其中userDao中的insert方法就是单个数据的插入方法
3.Mybatis的trim标签 3.1功能属性表 trim标签的功能属性如下表： 属性名 说明 prefix 在sql语句前面拼接的字符串 suffix 在sql语句后面拼接的字符串 prefixOverrides 去除sql语句前面指定的字符或者关键字 suffixOverrides 去除sql语句后面指定的字符或者关键字 3.2去除多余的and关键字1）先看下面最原始的sql： SELECT * FROM BLOG WHERE state = #{state} AND title like #{title} AND author_name like #{author.name}若这些条件没有一个能匹配上，那么这条 SQL 会变成： SELECT * FROM BLOG WHERE2）这种sql肯定会导致异常的出现。如果仅仅第二个条件匹配，那么这条 SQL 最终的样子: SELECT * FROM BLOG WHERE AND title like '%aaa%'这种sql就多了一个and关键字，也会导致异常的发生。 3）对于mybatis而言，可以使用where标签来解决上述的问题，其sql如下： SELECT * FROM BLOG state = #{state} AND title like #{title} AND author like #{author}4）除此之外，mybatis还提供了另一个标签trim，可以用其来去除多余的关键字，上一步的代码使用trim如下： SELECT * FROM BLOG state = #{state} AND title like #{title} AND author like #{author}上述的sql中，当条件中没有匹配的数据时，不会添加where关键字；当匹配到条件时，会在前面拼接where关键字，若匹配的条件前面有and，那么会自动去掉and关键字。举例说明，条件的内容只作为说明： A：当state、title、author都为NULL时，查询语句是 SELECT * FROM BLOGB：当title、author都为NULL而state不为NULL时，查询语句是： SELECT * FROM BLOG where state = 1C：当state、author都为NULL而title不为NULL时，去除了多余的and关键字，查询语句是： SELECT * FROM BLOG where title like '%aaa%'D：当state、title都不为NULL而author为NULL时，查询语句是： SELECT * FROM BLOG where state = 1 and title like '%aaa%' 3.3去除多余的逗号1）先看下面的sql： insert into BLOG ( state title, author ) values( #{state}, #{title}, #{author} )若这些条件中author没有匹配上，那么这条 SQL 会变成： insert into BLOG(state,title,) values(1,'mysql入门',);明显看出多了两个逗号，会导致异常。使用trim标签解决如下： insert into BLOG state title, author #{state}, #{title}, #{author}上述的sql中，当条件中没有匹配的数据时，不会插入对应的列数据。主要是使用suffixOverrides属性，当条件不满足时会删除结尾多余的逗号。 4.Oracle批量修改数据当数据量很大时，使用批量修改可以提高效率。看下面对用户信息进行批量修改： update user t when t.id = #{item.id} then #{item.name} when t.id = #{item.id} then #{item.password} when t.id = #{item.id} then #{item.addr} where id in #{item.id}使用了trim标签，生成的完整的sql如下，为了说明方便仅对三条数据进行修改，批量的数据是类似的： update user t set name = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , password = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , addr = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )在上述的sql中，使用了两层的trim标签，外层的标签添加了前缀"set"关键字，另外如果有多余的逗号则会删除。内层的trim对应一个字段，当此字段不为null时才会包含内部的代码；也使用了case when关键字。下面对几种情况进行分析： A：name为NULL，其他属性不为NULL时，实际的语句如下： update user t set password = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , addr = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )当属性为NULL时，此字段便不会被添加到sql语句当中，上述这种情况就是很好的说明。 B：password为NULL，其他属性不为NULL时，实际的语句 update user t set name = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , addr = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )C：addr为NULL，其他属性不为NULL时，实际的语句 update user t set name = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end , password = case when t.id = ? then ? when t.id = ? then ? when t.id = ? then ? end where id in ( ? , ? , ? )由于addr的前面有其他的trim，那么当addr为NULL时，就会自动删除关键字'end"后面的逗号。 5.MySQL批量修改数据MySQL的修改语法和oracle的语法基本类似，批量修改的语句可直接参考oracle的批量修改。 |
【本文地址】